<?php 
require_once dirname(__FILE__) . '/DbUtils.class.php';
require_once dirname(__FILE__) . '/metadata.php';
require_once dirname(__FILE__) . '/config.php';
require_once dirname(__FILE__) . '/includes/MiscUtils.class.php';
session_name(SESSION);
session_start();
if (!isset($_SESSION[CLIENT])) {
  return;
}
$myPdo = DbUtils::createPdoInst();

$start = MiscUtils::getParam('start', 0);
$end = MiscUtils::getParam('end', 0);
$class_id = MiscUtils::getParam('class_id', 0);

$class = NULL;
$cond_vals = new stdClass();
$cond_vals->c = 't.id = :v1';
$cond_vals->v = array(':v1' => $class_id);
$opts = new stdClass();
$opts->select_expr = 't.*, t1.attr3 AS teacher_name, t1.attr4 AS teacher_email';
$opts->join_expr = 'LEFT JOIN ' . TABLE_NAME_PREFIX_PLUGIN . 'users_users as t1 ON t1.id = t.attr6';
$tmp = DbUtils::get($myPdo, TABLE_NAME_PREFIX_PLUGIN . 'semester_classes', $cond_vals, NULL, NULL, NULL, NULL, $opts)->d;
if (count($tmp) > 0) {
  $class = $tmp[0];
}

if (!$class) {
  die('Class Not Found');
}

$cond_vals = new stdClass();
$cond_vals->c = 't.class_id = :v1 AND ((t.start <= :v2 AND t.end >=:v2) OR t.enrolled_semester_id != 0)';
$cond_vals->v = array(':v1' => $class_id, ':v2' => $start);
$opts = new stdClass();
$opts->select_expr = 't.enrolled_program_id, t.enrolled_semester_id, t1.*';
$opts->join_expr = 'LEFT JOIN ' . TABLE_NAME_PREFIX_PLUGIN . 'student_students as t1 ON t1.id = t.student_id';
$students = DbUtils::get($myPdo, TABLE_NAME_PREFIX_PLUGIN . 'student_enrolled_classes', $cond_vals, 't1.given_name', 'ASC', NULL, NULL, $opts)->d;
foreach ($students as $student) {
  $cond_vals = new stdClass();
  $cond_vals->c = 't.student_id = :v1 AND ((t.start >= :v2 AND t.end >= :v3 AND t.start < :v3) OR (t.start <= :v2 AND t.end < :v3 AND t.end > :v2) OR (t.start <= :v2 AND t.end >= :v3 ))';
  $cond_vals->v = array(':v1' => $student->id, ':v2' => $start, ':v3' => $end);
  $student->_notes = DbUtils::get($myPdo, TABLE_NAME_PREFIX_PLUGIN . 'student_notes', $cond_vals, NULL, NULL, NULL, NULL, NULL)->d;
}

$cond_vals = new stdClass();
$cond_vals->c = 't.attr1 = :v1';
$cond_vals->v = array(':v1' => $class_id);
$sessions = DbUtils::get($myPdo, TABLE_NAME_PREFIX_PLUGIN . 'semester_class_sessions', $cond_vals, 't.date1', 'ASC', NULL, NULL, NULL)->d;


$days = array();
for ($i = strtotime($start), $il = strtotime($end); $i <= $il; $i += 86400) {
  $days[date('w', $i)] = date('Y-m-d', $i);
}

foreach ($sessions as &$session) {
  $session->day = MiscUtils::getDay(date('w', strtotime($session->date1)));
  $session->time1 = date('H:i', strtotime($session->date1));
  $session->time2 = date('H:i', strtotime($session->date2));
  $session->actual_date = $days[date('w', strtotime($session->date1))];
}

date_default_timezone_set('Pacific/Auckland');

/** PHPExcel */
require_once dirname(__FILE__) . '/libs/phpexcel/PHPExcel.php';

// Create new PHPExcel object
$objPHPExcel = new PHPExcel();

$path = dirname(__FILE__) . '/tmp/';
$file = 'ATT_' . $class->teacher_name . '_' . $class->attr2 . ((trim($class->attr8) == '') ? '' : ' ' . $class->attr8 ). '_' . $start . '_' . $end . '.xlsx';

$info = new stdClass();
$info->class_id = $class->id;
$info->secret = SECRET;
$info->sessions = count($sessions);
$info->students = count($students);
$info->start = $start;
$info->end = $end;
// Set properties
$objPHPExcel->getProperties()->setCreator("$class->teacher_name")
               ->setLastModifiedBy("$class->teacher_name")
               ->setTitle($class->attr2 . ((trim($class->attr8) == '') ? '' : ' ' . $class->attr8 ))
               ->setSubject($start . ' to ' . $end)
               ->setDescription(json_encode($info))
               ->setKeywords('')
               ->setCategory("Office 2007 XLSX Document");

// Rename sheet
$objPHPExcel->getActiveSheet()->setTitle("$start $end");

$horizontal = 67;
$vertical = 4;

$objPHPExcel->getActiveSheet()->mergeCells('A1:' . chr(count($sessions) + $horizontal) . '1');
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('A1', 'If a student is absent, mark "a". If late, mark "l" DO NOT edit ANYTHING else');

foreach ($sessions as $idx => &$session) {
  $objPHPExcel->setActiveSheetIndex(0)->setCellValue(chr($idx + $horizontal) . '2', $session->id . '#' . date('Y-m-d', strtotime($session->actual_date)));
  $objPHPExcel->setActiveSheetIndex(0)->setCellValue(chr($idx + $horizontal) . '3', $session->day . ' ' . $session->time1 . '-' . $session->time2);
  $objPHPExcel->getActiveSheet()->getColumnDimension(chr($idx + $horizontal))->setWidth(15);
}

foreach ($students as $idx => &$student) {
  $objPHPExcel->setActiveSheetIndex(0)->setCellValue(chr($horizontal - 2) . ($idx + $vertical), $student->id . '#' . $student->id_number);
  $objPHPExcel->getActiveSheet()->getColumnDimension(chr($horizontal - 2))->setWidth(15);
  $objPHPExcel->setActiveSheetIndex(0)->setCellValue(chr($horizontal - 1) . ($idx + $vertical), $student->given_name . ' ' . $student->family_name . ' (' . $student->preferred_name . ')');
  $objPHPExcel->getActiveSheet()->getColumnDimension(chr($horizontal - 1))->setWidth(20);
  foreach ($student->_notes as $note) {
    foreach ($sessions as $idx2 => &$session) {
      if (strtotime($session->actual_date) >= strtotime($note->start) && strtotime($session->actual_date) <= strtotime($note->end)) {
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue(chr($idx2 + $horizontal) . ($idx + $vertical), $note->reason_name);
      }
    }
  }
}

// Set active sheet index to the first sheet, so Excel opens this as the first sheet
$objPHPExcel->setActiveSheetIndex(0);

$obj = new stdClass();
$obj->track_id = MiscUtils::unique();
$obj->class_id = $class_id;
$obj->start = $start;
$obj->end = $end;
$obj->date = date('Y-m-d', time());
$obj->text = 'Roster is downloaded';
$obj->is_uploaded = 0;
$obj->file = '';
$obj->path = '';
DbUtils::add($myPdo, TABLE_NAME_PREFIX_PLUGIN . 'attendences_logs', $obj);

// Redirect output to a client’s web browser (Excel2007)
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="' . $file . '"');
header('Cache-Control: max-age=0');
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
$objWriter->save('php://output');
?>